{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "9c84ebb9",
   "metadata": {},
   "outputs": [],
   "source": [
    "import openpyxl\n",
    "import smtplib\n",
    "from email.mime.text import MIMEText"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "2f4d1498",
   "metadata": {},
   "outputs": [
    {
     "ename": "FileNotFoundError",
     "evalue": "[Errno 2] No such file or directory: 'gongzi.xlsx'",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mFileNotFoundError\u001b[0m                         Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-8-7167cf6a3df1>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m     77\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     78\u001b[0m \u001b[1;32mif\u001b[0m \u001b[0m__name__\u001b[0m \u001b[1;33m==\u001b[0m \u001b[1;34m'__main__'\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 79\u001b[1;33m     \u001b[0mdatas\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mget_data\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m          \u001b[1;31m# 获取excel数据，转换为html\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m     80\u001b[0m     \u001b[1;32mfor\u001b[0m \u001b[0mdata\u001b[0m \u001b[1;32min\u001b[0m \u001b[0mdatas\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     81\u001b[0m         \u001b[0msend_to_list\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;33m[\u001b[0m\u001b[0mdata\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;33m-\u001b[0m\u001b[1;36m1\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m<ipython-input-8-7167cf6a3df1>\u001b[0m in \u001b[0;36mget_data\u001b[1;34m()\u001b[0m\n\u001b[0;32m     48\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     49\u001b[0m \u001b[1;32mdef\u001b[0m \u001b[0mget_data\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 50\u001b[1;33m     \u001b[0mwb\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mopenpyxl\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mload_workbook\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'gongzi.xlsx'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m     51\u001b[0m     \u001b[0mws\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mwb\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'Sheet1'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     52\u001b[0m     \u001b[0mall_data\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;33m[\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3.3\\lib\\site-packages\\openpyxl\\reader\\excel.py\u001b[0m in \u001b[0;36mload_workbook\u001b[1;34m(filename, read_only, keep_vba, data_only, keep_links)\u001b[0m\n\u001b[0;32m    313\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    314\u001b[0m     \"\"\"\n\u001b[1;32m--> 315\u001b[1;33m     reader = ExcelReader(filename, read_only, keep_vba,\n\u001b[0m\u001b[0;32m    316\u001b[0m                         data_only, keep_links)\n\u001b[0;32m    317\u001b[0m     \u001b[0mreader\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mread\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3.3\\lib\\site-packages\\openpyxl\\reader\\excel.py\u001b[0m in \u001b[0;36m__init__\u001b[1;34m(self, fn, read_only, keep_vba, data_only, keep_links)\u001b[0m\n\u001b[0;32m    122\u001b[0m     def __init__(self,  fn, read_only=False, keep_vba=KEEP_VBA,\n\u001b[0;32m    123\u001b[0m                   data_only=False, keep_links=True):\n\u001b[1;32m--> 124\u001b[1;33m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0marchive\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0m_validate_archive\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mfn\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    125\u001b[0m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mvalid_files\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0marchive\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mnamelist\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    126\u001b[0m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mread_only\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mread_only\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3.3\\lib\\site-packages\\openpyxl\\reader\\excel.py\u001b[0m in \u001b[0;36m_validate_archive\u001b[1;34m(filename)\u001b[0m\n\u001b[0;32m     94\u001b[0m             \u001b[1;32mraise\u001b[0m \u001b[0mInvalidFileException\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mmsg\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     95\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 96\u001b[1;33m     \u001b[0marchive\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mZipFile\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mfilename\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;34m'r'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m     97\u001b[0m     \u001b[1;32mreturn\u001b[0m \u001b[0marchive\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     98\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3.3\\lib\\zipfile.py\u001b[0m in \u001b[0;36m__init__\u001b[1;34m(self, file, mode, compression, allowZip64, compresslevel, strict_timestamps)\u001b[0m\n\u001b[0;32m   1249\u001b[0m             \u001b[1;32mwhile\u001b[0m \u001b[1;32mTrue\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1250\u001b[0m                 \u001b[1;32mtry\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1251\u001b[1;33m                     \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mfp\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mio\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mopen\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mfile\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mfilemode\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1252\u001b[0m                 \u001b[1;32mexcept\u001b[0m \u001b[0mOSError\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1253\u001b[0m                     \u001b[1;32mif\u001b[0m \u001b[0mfilemode\u001b[0m \u001b[1;32min\u001b[0m \u001b[0mmodeDict\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mFileNotFoundError\u001b[0m: [Errno 2] No such file or directory: 'gongzi.xlsx'"
     ]
    }
   ],
   "source": [
    "host = 'smtp.163.com'           # 设置发件服务器地址\n",
    "port = 465                      # 设置发件服务器端口号\n",
    "sender = 'xxxyyy@163.com'       # 设置发件邮箱\n",
    "pwd = 'aaa20190105'             # 设置发件邮箱授权码\n",
    "send_to_sub = 'x月工资条'\n",
    "\n",
    "\n",
    "def get_html(person_data):\n",
    "    html_content = \"\"\"\n",
    "<html>\n",
    "    <head>\n",
    "        <meta http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\" />\n",
    "        <title>工资条</title>\n",
    "    </head>\n",
    "    <body>\n",
    "        <div id=\"container\">\n",
    "            <p><strong>工资条：</strong></p>\n",
    "            <div id=\"content\">\n",
    "                <table width=\"500\" border=\"2\">\n",
    "                    <tr>\n",
    "                        <td><strong>姓名</strong></td>\n",
    "                        <td><strong>月份</strong></td>\n",
    "                        <td><strong>部门</strong></td>\n",
    "                        <td><strong>工号</strong></td>\n",
    "                        <td><strong>基本工资</strong></td>\n",
    "                        <td><strong>岗位工资</strong></td>\n",
    "                        <td><strong>绩效工资</strong></td>\n",
    "                        <td><strong>税前</strong></td>\n",
    "                    </tr>\n",
    "                    <tr>\n",
    "                        <td>\"\"\" + person_data[0] + \"\"\"</td>\n",
    "                        <td>\"\"\" + person_data[1] + \"\"\"</td>\n",
    "                        <td>\"\"\" + person_data[2] + \"\"\"</td>\n",
    "                        <td>\"\"\" + person_data[3] + \"\"\"</td>\n",
    "                        <td>\"\"\" + person_data[4] + \"\"\"</td>\n",
    "                        <td>\"\"\" + person_data[5] + \"\"\"</td>\n",
    "                        <td>\"\"\" + person_data[6] + \"\"\"</td>\n",
    "                        <td>\"\"\" + person_data[7] + \"\"\"</td>\n",
    "                    </tr>\n",
    "                </table>\n",
    "            </div>\n",
    "        </div>\n",
    "    </body>\n",
    "</html>\n",
    "\"\"\"\n",
    "    return html_content\n",
    "\n",
    "\n",
    "def get_data():\n",
    "    wb = openpyxl.load_workbook('gongzi.xlsx')\n",
    "    ws = wb['Sheet1']\n",
    "    all_data = []\n",
    "    for row in range(2, ws.max_row + 1):\n",
    "        per_list = []\n",
    "        for cell in ws[row]:\n",
    "            per_list.append(str(cell.value))\n",
    "        all_data.append(per_list)\n",
    "    return all_data\n",
    "\n",
    "\n",
    "def send_mail(to_list, sub, content):\n",
    "    me = \"<\"+sender+\">\"\n",
    "    msg = MIMEText(content, _subtype='html', _charset='utf-8')\n",
    "    msg['Subject'] = sub\n",
    "    msg['From'] = me\n",
    "    msg['To'] = \";\".join(to_list)\n",
    "    try:\n",
    "        server = smtplib.SMTP()\n",
    "        server.connect(host)\n",
    "        server.login(sender, pwd)\n",
    "        server.sendmail(me, to_list, msg.as_string())\n",
    "        server.close()\n",
    "        return True\n",
    "    except smtplib.SMTPException as e:\n",
    "        print(str(e))\n",
    "        return False\n",
    "\n",
    "if __name__ == '__main__':\n",
    "    datas = get_data()          # 获取excel数据，转换为html\n",
    "    for data in datas:\n",
    "        send_to_list = [data[-1]]\n",
    "        send_to_html = get_html(data)\n",
    "        if send_mail(send_to_list, send_to_sub, send_to_html):\n",
    "            print(\"发送成功\")\n",
    "        else:\n",
    "            print(\"发送失败\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2864ac82",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
